
import java.awt.BorderLayout;
import java.awt.Component;
import java.awt.EventQueue;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.border.EmptyBorder;
import javax.swing.filechooser.FileFilter;
import javax.swing.table.AbstractTableModel;
import javax.swing.table.DefaultTableCellRenderer;
//thu vien duoi day dung de MigLayout căn lề
//import net.miginfocom.swing.MigLayout;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;

public class BalanceImportExcelFrame extends JFrame {

	private JPanel contentPane;
	private JTable table;
	private JFileChooser fileChooiser;

	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					BalanceImportExcelFrame frame = new BalanceImportExcelFrame();
					frame.setTitle("Import from Excel");
					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the frame.
	 */
	public BalanceImportExcelFrame() {
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 782, 489);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		contentPane.setLayout(new BorderLayout(0, 0));
		setContentPane(contentPane);

		JPanel panel = new JPanel();
		contentPane.add(panel, BorderLayout.NORTH);
		//panel.setLayout(new MigLayout("", "[124px]", "[14px][]"));

		JLabel lblNewLabel = new JLabel(
				"Nhập bản cân đối kế toán từ file excel ");
		panel.add(lblNewLabel, "cell 0 0,growx,aligny top");

		JButton btnLoad = new JButton("Nhập file excel...");
		btnLoad.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				loadExcel();
			}
		});
		panel.add(btnLoad, "flowx,cell 0 1");

		JButton btnSave = new JButton("Lưu");
		btnSave.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				save();
			}
		});

		panel.add(btnSave, "cell 0 1");

		JButton btnCancel = new JButton("Hủy");
		btnCancel.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				resetTable();
			}
		});
		panel.add(btnCancel, "cell 0 1");

		JButton btnExit = new JButton("Thoát");
		btnExit.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				System.exit(0);
			}
		});
		panel.add(btnExit, "flowx,cell 5 1");

		JScrollPane scrollPane = new JScrollPane();
		contentPane.add(scrollPane, BorderLayout.CENTER);

		table = new JTable();

		scrollPane.setViewportView(table);

		//
		this.resetTable();
		//

	}

	public void loadExcel() {
		if (this.fileChooiser == null) {
			this.fileChooiser = new JFileChooser();
			this.fileChooiser.setFileFilter(new FileFilter() {

				@Override
				public boolean accept(File f) {
					if (f.isDirectory()) {
						return true;
					}
					String fileName = f.getName();
					if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
						return true;
					}
					return false;
				}

				@Override
				public String getDescription() {
					return "Microsoft excel (*.xls,*.xlsx)";
				}

			});
		}
		int i = this.fileChooiser.showOpenDialog(this);
		if (i != JFileChooser.APPROVE_OPTION) {
			return;
		}
		File file = this.fileChooiser.getSelectedFile();
		try {
			if (file != null) {
				BalanceExcelReader reader = new BalanceExcelReader();
				List<Balance> list = reader.readExcell(file);
				BalanceTableModel model = new BalanceTableModel(list);
				table.setModel(model);

				table.getColumnModel().getColumn(4)
						.setCellRenderer(new NumberRenderer());
				table.getColumnModel().getColumn(5)
						.setCellRenderer(new NumberRenderer());
			}
		} catch (Exception e) {
			e.printStackTrace();
			JOptionPane.showMessageDialog(this, "Lỗi " + e.getMessage(),
					"Error", JOptionPane.ERROR_MESSAGE);
		}

	}

	private void resetTable() {
		table.setModel(new BalanceTableModel(new ArrayList<Balance>()));
	}

	private void save() {
		BalanceTableModel model = (BalanceTableModel) table.getModel();
		List<Balance> list = model.getData();
		DBUtils utils = DBUtils.getInstance();
		try {
			utils.saveBalances(list);
		} catch (Throwable e) {
			e.printStackTrace();
			JOptionPane.showMessageDialog(this, "Lỗi " + e.getMessage(),
					"Error", JOptionPane.ERROR_MESSAGE);
		}
	}

}

class Balance {

	private String STT;
	private String taiSan;

	private String maSo;
	private String thuyetMinh;
	private Double soCuoiKy;
	private Double soDauKy;

	public String getSTT() {
		return STT;
	}

	public void setSTT(String stt) {
		this.STT = stt;
	}

	public String gettaiSan() {
		return taiSan;
	}

	public void settaiSan(String taisan) {
		this.taiSan = taisan;
	}

	public String getMaSo() {
		return maSo;
	}

	public void setMaSo(String maSo) {
		this.maSo = maSo;
	}

	public String getThuyetMinh() {
		return thuyetMinh;
	}

	public void setThuyetMinh(String thuyetMinh) {
		this.thuyetMinh = thuyetMinh;
	}

	public Double getSoCuoiKy() {
		return soCuoiKy;
	}

	public void setSoCuoiKy(Double soCuoiKy) {
		this.soCuoiKy = soCuoiKy;
	}

	public Double getSoDauKy() {
		return soDauKy;
	}

	public void setSoDauKy(Double soDauKy) {
		this.soDauKy = soDauKy;
	}
}

class DBUtils {
	private static DBUtils instance;

	private DBUtils() {

	}

	public Connection getConnection() throws ClassNotFoundException,
			SQLException {
		// Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		// org.postgresql.Driver
		Class.forName("org.postgresql.Driver");
		Connection con = DriverManager
				.getConnection("jdbc:postgresql://172.16.0.2/adempiere?user=adempiere&password=adempiere");
		return con;
	}

	public static DBUtils getInstance() {
		if (instance == null) {
			instance = new DBUtils();
		}
		return instance;
	}

	public void saveBalances(List<Balance> list) throws ClassNotFoundException,
			SQLException {

		int u_frheader_id = 134325; //thay so nay bang bien global de thay cho các ID khác

		Connection conn = this.getConnection();
		conn.setAutoCommit(false);
		PreparedStatement pstm1 = conn
				.prepareStatement("Update  u_balancesheet  set thuyetminh=?,so_dauky=?,so_cuoiky=? "
						+ " where u_frheader_id =? and u_bscode_id = ? ");

		//System.out.println("u_frheader_id = " + String.valueOf(u_frheader_id));
		PreparedStatement pstm2 = conn
				.prepareStatement("Insert into u_balancesheet(thuyetminh,so_dauky,so_cuoiky) values (?,?,?)");
		for (Balance b : list) {
			pstm1.setString(5, b.getMaSo());
			//System.out.println("Mã số update =" + b.getMaSo());
			//System.out.println("Tên Tài Sản update =" + b.gettaiSan());
			pstm1.setString(1, b.getThuyetMinh());

			//System.out.println("Thuyết Minh update =" + b.getThuyetMinh());
			pstm1.setDouble(2, b.getSoDauKy());
			//System.out.println("Số đầu kỳ update  =" + b.getSoDauKy().toString()+" / id ="+ u_frheader_id);
			pstm1.setDouble(3, b.getSoCuoiKy());
			//System.out.println("Số cuối kỳ update  =" + b.getSoCuoiKy().toString());
			pstm1.setInt(4, u_frheader_id);
			int i = pstm1.executeUpdate();
			
			System.out.println("Ket qua UPDATE ="+i); 
			//System.out.println("i  =" + i);
			

			// if (i != 1) {
			// pstm2.setString(1, b.getMaSo());
			// pstm2.setString(1, b.getThuyetMinh());
			// pstm2.setDouble(2, b.getSoDauKy());
			// pstm2.setDouble(3, b.getSoCuoiKy());
			// pstm2.executeUpdate();
			// }
		}
		System.out.println("OK1");
		conn.commit();
		pstm1.close();
		pstm2.close();
		conn.close();
	}

}

abstract class AbstractExcelReader {

	private int index = 0;
	private int rowIndex = 0;
	private Map<Integer, Integer> cellTypeMap = new HashMap<Integer, Integer>();
	private Map<Integer, String> titleMap = new HashMap<Integer, String>();

	private Iterator<Row> rowIterator;
	private boolean ready = false;

	public AbstractExcelReader(String[] titles, int[] cellTypes) {
		if (titles == null || titles.length == 0) {
			throw new NullPointerException("Excel không có tiêu đề");
		}
		if (cellTypes == null) {
			throw new NullPointerException("Excel không format cell");
		}
		if (titles.length != cellTypes.length) {
			throw new IllegalArgumentException("Đặt lại độ lớn của mảng");
		}
		for (int i = 0; i < titles.length; i++) {
			this.defineColumn(titles[i], cellTypes[i]);
		}
	}

	public AbstractExcelReader() {

	}

	protected String cellTypeToString(int cellType) {
		switch (cellType) {
		case HSSFCell.CELL_TYPE_BLANK:
			return "blank";
		case HSSFCell.CELL_TYPE_BOOLEAN:
			return "boolean";
		case HSSFCell.CELL_TYPE_ERROR:
			return "error";
		case HSSFCell.CELL_TYPE_FORMULA:
			return "formula";
		case HSSFCell.CELL_TYPE_NUMERIC:
			return "numeric";
		case HSSFCell.CELL_TYPE_STRING:
			return "text";
		default:
			return "unknow";
		}

	}

	public void reset() {
		this.ready = false;
		this.titleMap = new HashMap<Integer, String>();
		this.cellTypeMap = new HashMap<Integer, Integer>();
		this.rowIndex = 0;
		this.index = 0;
	}

	private void exceptionReady() {
		if (this.ready) {
			throw new IllegalStateException("Illigel state");
		}
	}

	/**
	 * 
	 * 
	 * @param headerTitle
	 * @param cellType
	 *            - Xem HSSFCell.CELL_TYPE_STRING,...
	 */
	protected void defineColumn(String headerTitle, int cellType) {
		this.exceptionReady();
		if (headerTitle == null) {
			throw new NullPointerException("Title excel null");
		}
		this.titleMap.put(index, headerTitle);
		this.cellTypeMap.put(index, cellType);
		index++;
	}

	public ByteArrayOutputStream exportTemplate() throws IOException {
		return this.exportTemplate(null);
	}

	public ByteArrayOutputStream exportTemplate(String sheetName)
			throws IOException {
		HSSFWorkbook workBook = new HSSFWorkbook();
		sheetName = sheetName == null ? "Data" : sheetName;
		HSSFSheet sheet = workBook.createSheet(sheetName);
		HSSFRow row = sheet.createRow(0);
		int columnCount = this.titleMap.size();
		for (int i = 0; i < columnCount; i++) {
			HSSFCell cell = row.createCell(i, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(this.titleMap.get(i));
		}
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		workBook.write(baos);
		return baos;
	}

	public void parseExcel(File excelFile, int sheetIndex, int rowStart,
			boolean exceptionOnInvalid) throws IOException,
			ExcelInvalidException {
		InputStream inputStream = new FileInputStream(excelFile);

		POIFSFileSystem fileSystem = new POIFSFileSystem(inputStream);

		HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
		HSSFSheet sheet = workBook.getSheetAt(sheetIndex);

		Iterator<Row> rows = sheet.rowIterator();
		if (exceptionOnInvalid) {

			if (rows.hasNext()) {
				HSSFRow headerRow = (HSSFRow) rows.next();
				int columnCount = this.titleMap.size();
				for (int i = 0; i < columnCount; i++) {
					HSSFCell cell = headerRow.getCell(i);
					if (cell == null) {
						throw new ExcelInvalidException(
								"Excel invalid not found column "
										+ this.titleMap.get(i));
					}
					if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING) {
						throw new ExcelInvalidException(
								"Excel invalid. Column " + this.titleMap.get(i)
										+ " not text format");
					}
					String value = cell.getStringCellValue();
					if (this.titleMap.equals(value)) {
						throw new ExcelInvalidException(
								"Excel invalid. Column " + (i + 1) + " must "
										+ this.titleMap.get(i));
					}
				}
			}
		}
		inputStream.close();

		this.rowIterator = rows;
		this.rowIndex = 1;
		for (int i = 0; i < rowStart; i++) {
			this.rowIterator.next();
			this.rowIndex++;
		}
	}

	protected boolean hasRow() {
		return this.rowIterator != null && this.rowIterator.hasNext();
	}

	protected Map<String, Object> nextRow() throws ExcelCellInvalidException {
		HSSFRow row = (HSSFRow) this.rowIterator.next();
		this.rowIndex++;
		if (row == null) {
			return null;
		}
		int columnCount = this.titleMap.size();
		Map<String, Object> map = new HashMap<String, Object>(columnCount);
		for (int i = 0; i < columnCount; i++) {
			HSSFCell cell = row.getCell(i);
			Object value = null;
			if (cell == null) {
				value = null;
			} else {
				try {
					cell.setCellType(this.cellTypeMap.get(i));
				} catch (Exception e) {
				}
				int cellType = cell.getCellType();
				if (cellType != this.cellTypeMap.get(i)) {
					System.out.println(" Required Type: "
							+ this.cellTypeToString(this.cellTypeMap.get(i)));
					System.out.println(" Found Type: "
							+ this.cellTypeToString(cellType));
					throw new ExcelCellInvalidException("Cell at (row,col)=("
							+ rowIndex + "," + (rowIndex + 1)
							+ ") must format type: "
							+ this.cellTypeToString(this.cellTypeMap.get(i)));
				}

				switch (cellType) {
				case HSSFCell.CELL_TYPE_BLANK:
					value = null;
					break;
				case HSSFCell.CELL_TYPE_BOOLEAN:
					value = cell.getBooleanCellValue();
					break;
				case HSSFCell.CELL_TYPE_ERROR:
					value = cell.getErrorCellValue();
					break;
				case HSSFCell.CELL_TYPE_FORMULA:
					// TODO Xem lai cho nay.
					value = null;
					break;
				case HSSFCell.CELL_TYPE_NUMERIC:
					value = cell.getNumericCellValue();
					break;
				case HSSFCell.CELL_TYPE_STRING:
					value = cell.getStringCellValue();
					break;
				default:
					return null;
				}
				map.put(this.titleMap.get(i), value);
			}
		}
		return map;
	}

}

class ExcelCellInvalidException extends Exception {

	private static final long serialVersionUID = 1L;

	public ExcelCellInvalidException(String message) {
		super(message);
	}
}

class ExcelInvalidException extends Exception {

	private static final long serialVersionUID = 1L;

	public ExcelInvalidException(String message) {
		super(message);
	}
}

class NumberRenderer extends DefaultTableCellRenderer {

	private static final long serialVersionUID = 1L;

	private NumberFormat formatter;

	public NumberRenderer() {

		formatter = NumberFormat.getNumberInstance(Locale.GERMAN);
	}

	public Component getTableCellRendererComponent(JTable table, Object value,
			boolean isSelected, boolean hasFocus, int row, int column) {
		Component c = super.getTableCellRendererComponent(table, value,
				isSelected, hasFocus, row, column);

		if (value instanceof Double) {
			String s = formatter.format((Double) value);
			if (c instanceof JLabel) {
				((JLabel) c).setText(s);
				((JLabel) c).setHorizontalAlignment(JLabel.RIGHT);
			}
		}
		return c;
	}
}

class BalanceExcelReader extends AbstractExcelReader {

	public static final String COLUMN_STT = "STT";

	public static final String COLUMN_TAI_SAN = "TAI_SAN";
	public static final String COLUMN_MA_SO = "MA_SO";
	public static final String COLUMN_THUYET_MINH = "THUYET_MINH";
	public static final String COLUMN_SO_CUOI_NAM = "SO_CUOI_NAM";
	public static final String COLUMN_SO_DAU_NAM = "SO_DAU_NAM";

	public BalanceExcelReader() {
		this.defineColumn(COLUMN_STT, HSSFCell.CELL_TYPE_STRING);
		this.defineColumn(COLUMN_TAI_SAN, HSSFCell.CELL_TYPE_STRING);
		this.defineColumn(COLUMN_MA_SO, HSSFCell.CELL_TYPE_STRING);
		this.defineColumn(COLUMN_THUYET_MINH, HSSFCell.CELL_TYPE_STRING);
		this.defineColumn(COLUMN_SO_CUOI_NAM, HSSFCell.CELL_TYPE_NUMERIC);
		this.defineColumn(COLUMN_SO_DAU_NAM, HSSFCell.CELL_TYPE_NUMERIC);
	}

	/**
	 * 
	 * @param excelFile
	 * @throws Exception
	 */

	public List<Balance> readExcell(File excelFile) throws Exception {
		// Doc Sheet thu 1 (index=0)
		// Bat dau tu dong thu 9 (index =8)
		// Khong kiem tra cac dong tieu de.
		this.parseExcel(excelFile, 0, 8, false);
		List<Balance> list = new ArrayList<Balance>();

		while (this.hasRow()) {
			Map<String, Object> row = this.nextRow();
			Balance balanceRow = new Balance();
			String stt = (String) row.get(COLUMN_STT);
			if (stt != null && !stt.trim().equals("")) {

				balanceRow.setSTT(stt);
				balanceRow.settaiSan((String) row.get(COLUMN_TAI_SAN));

				balanceRow.setMaSo((String) row.get(COLUMN_MA_SO));
				balanceRow.setThuyetMinh((String) row.get(COLUMN_THUYET_MINH));
				balanceRow.setSoCuoiKy((Double) row.get(COLUMN_SO_CUOI_NAM));
				balanceRow.setSoDauKy((Double) row.get(COLUMN_SO_DAU_NAM));
				System.out.println("Row So_cuoi_nam ="
						+ row.get(COLUMN_SO_CUOI_NAM));

				list.add(balanceRow);
			}
		}
		return list;
	}
}

class BalanceTableModel extends AbstractTableModel {

	private static final long serialVersionUID = 1L;
	private List<Balance> list = new ArrayList<Balance>();

	public BalanceTableModel(List<Balance> list) {
		this.list = list;
	}

	public List<Balance> getData() {
		return this.list;
	}

	@Override
	public Object getValueAt(int row, int column) {
		Balance b = list.get(row);
		switch (column) {
		case 0:
			return b.getSTT();
		case 1:
			return b.gettaiSan();
		case 2:
			return b.getMaSo();
		case 3:
			return b.getThuyetMinh();
		case 4:
			return b.getSoCuoiKy();
		case 5:
			return b.getSoDauKy();
		default:
			return "Không có data";
		}
	}

	@Override
	public int getRowCount() {
		return list.size();

	}

	@Override
	public String getColumnName(int column) {
		switch (column) {
		case 0:
			return "STT";
		case 1:
			return "Tài Sản";
		case 2:
			return "Mã số";
		case 3:
			return "Thuyết Minh";
		case 4:
			return "Số cuối kỳ";
		case 5:
			return "Số đầu kỳ";
		default:
			return "none";
		}
	}

	@Override
	public int getColumnCount() {
		return 6;
	}

	@Override
	public void setValueAt(Object aValue, int row, int column) {
		super.setValueAt(aValue, row, column);
	}
}
